------------------------------------------------------------------------
-- Script:			job-analysis.sql
-- Version:			1.1
-- Release Date:	2013-01-03
-- Author:			Davide Mauri (SolidQ)
-- Credits:			-
-- License:			Microsoft Public License (Ms-PL)
-- Target Version:	SQL Server 2008 RTM or above
-- Tab/indent size:	4
------------------------------------------------------------------------
use msdb
go



/*
	Function to convert the integer value used to represent time in msdb to a proper time datatype
*/

if (OBJECT_ID('dbo.fn_ConvertMSDBTimeToSQLTime', 'IF') is not null) drop function dbo.fn_ConvertMSDBTimeToSQLTime
go

create function dbo.fn_ConvertMSDBTimeToSQLTime(@msdb_time as int)
returns table
as
return 
select [sql_time] = cast(
	LEFT(right('000000' + cast(@msdb_time as varchar(6)), 6),2) + ':' + 
	SUBSTRING(right('000000' + cast(@msdb_time as varchar(6)), 6), 3, 2) + ':' +
	RIGHT(right('000000' + cast(@msdb_time as varchar(6)), 6), 2)
	as time(0)
)
go



/*
	Function to convert the integer value used to represent duration in msdb (HHMMSS) to duration expressed in minutes
*/

if (OBJECT_ID('dbo.fn_DecodeRunDuration', 'IF') is not null) drop function dbo.fn_DecodeRunDuration
go

create function dbo.fn_DecodeRunDuration(@run_duration as int)
returns table
as
return 
with cte as 
(
	select run_duration_string = right('000000000000' + cast(@run_duration as varchar(12)), 12)
),
cte2 as
(
	select run_duration_string, hour_start_position = LEN(run_duration_string)-3 from cte
),
cte3 as
(
	select
		run_hours = cast(LEFT(run_duration_string, hour_start_position - 1) as int),
		run_minutes = cast(SUBSTRING(run_duration_string, hour_start_position, 2) as int),
		run_seconds = cast(RIGHT(run_duration_string, 2) as int)
	from
		cte2
)
select run_duration_min = run_hours * 60 + run_minutes + run_seconds / 60--, *
from
	cte3
go


/*
	Function to decode the numeric values for run_status to a more descriptive values
	using the information available here http://msdn.microsoft.com/en-us/library/ms174997.aspx
*/

if (OBJECT_ID('dbo.fn_DecodeRunStatus', 'IF') is not null) drop function dbo.fn_DecodeRunStatus
go

create function dbo.fn_DecodeRunStatus(@run_status as int)
returns table
as
return 
	select 
		run_status = case @run_status
						 when 0 then 'Failed'
						 when 1 then 'Succeeded'
						 when 2 then 'Retry'
						 when 3 then 'Canceled'
						 else 'Unknown'
					end	
go



/*
	View to return all job categories
*/

if (OBJECT_ID('dbo.vw_job_categories', 'V') is not null) drop view dbo.vw_job_categories
go

create view dbo.vw_job_categories
as
select
	category_id,
	name
from
	dbo.syscategories
go



/*
	View to return all jobs
*/

if (OBJECT_ID('dbo.vw_jobs', 'V') is not null) drop view dbo.vw_jobs
go

create view dbo.vw_jobs
as
select
	job_id,
	category_id,
	name
from
	dbo.sysjobs
go
 

 
/*
	View to return all job executions
*/

if (OBJECT_ID('dbo.vw_job_executions', 'V') is not null) drop view dbo.vw_job_executions
go

create view dbo.vw_job_executions
as
select 	
	execution_id = h1.instance_id,
	h1.job_id,
	execution_run_date = cast(CAST(h1.run_date as varchar(8)) as date),
	execution_run_time = c1.[sql_time],
	execution_run_status =  c2.run_status,
	execution_duration_min = c3.run_duration_min,
	from_instance_id = n.instance_id + 1,
	to_instance_id = h1.instance_id
from 
	dbo.sysjobhistory h1 
cross apply
	(select top 1 instance_id from dbo.sysjobhistory h2 where h2.job_id = h1.job_id and h2.instance_id < h1.instance_id and h2.step_id = 0 order by instance_id desc) n
cross apply
	dbo.fn_ConvertMSDBTimeToSQLTime(h1.run_time) c1
cross apply
	dbo.fn_DecodeRunStatus(h1.run_status) c2
cross apply
	dbo.fn_DecodeRunDuration(h1.run_duration) c3
where 
	h1.step_id = 0
go



/*
	View to return all step executed along with the job and the execution (or instance) id to which they belong to
*/

if (OBJECT_ID('dbo.vw_steps', 'V') is not null) drop view dbo.vw_steps
go

create view dbo.vw_steps
as
with cte as
(
	select 	
		instance_id,
		job_id,
		step_id,
		step_name,
		run_date = cast(CAST(h1.run_date as varchar(8)) as date),
		run_time = c1.[sql_time],
		run_status =  c2.run_status,
		duration_min = c3.run_duration_min
	from 
		dbo.sysjobhistory h1 
	cross apply
		dbo.fn_ConvertMSDBTimeToSQLTime(h1.run_time) c1
	cross apply
		dbo.fn_DecodeRunStatus(h1.run_status) c2
	cross apply
		dbo.fn_DecodeRunDuration(h1.run_duration) c3
	where 
		h1.step_id > 0
)
select
	execution_id,
	instance_id,
	s.job_id,
	step_id,
	step_name,
	run_date,
	run_time,
	run_status,
	duration_min
from
	cte s
inner join
	dbo.vw_job_executions e on s.job_id = e.job_id and s.instance_id between e.from_instance_id and e.to_instance_id
go